SQLAlchemy Constraints

Takeaways

• Column constraints ensure data integrity across our database, allowing for database accuracy and consistency.
• Constraints are conditions on your column, that provide checks on the data's validity. It does not allow data that violates constraints to be inserted into the database (it will raise an error if you attempt to).
• In SQLAlchemy, constraints are set in db.Column() after setting the data type.
  • nullable=False is equivalent to NOT NULL in SQL
  • unique=True is equivalent to UNIQUE in SQL

Example

class User(db.Model):
  ...
  name = db.Column(db.String(), nullable=False, unique=True)

Implementing a check constraint

Example of how you would implement a constraint on an integer: using db.CheckConstraint

class Product(db.Model):
  ...
  price = db.Column(db.Float, db.CheckConstraint('price>0'))

This ensures that no product goes into the table with a nonpositive price value.

Resources

• SQLAlchemy Constraints Docs . Constraints available in SQLAlchemy are (generally) available in Flask-SQLAlchemy, and exposed by db.<sqlalchemy_method_or_interface> .

QUIZ QUESTION: :

Match the SQL constraint to the SQLAlchemy ORM Constraint option

ANSWER CHOICES:

NOT NULL

UNIQUE

SQL	SQLAlchemy ORM
nullable=True
nullable=False
unique=True
constraint="not_null"
constraint="unique"

SOLUTION:

SQL	SQLAlchemy ORM
nullable=False	NOT NULL
unique=True	UNIQUE